package com.supermap.application.dao;

import com.supermap.application.entity.TIndexSysTypeColData;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 指标数据查询
 */
@Repository
public interface TargetManageIndexSysDataDao extends JpaRepository<TIndexSysTypeColData,String> {

    //根据指标code、时间、地区名称查询
    @Query("select t.index_name, t.time, t.sj_zs, t.sj_wcl, t.jh_zs,t.diqu_name from TIndexSysTypeColData t where t.index_code = :index_code and t.time = :time and t.diqu_name = :diquName ")
    List<TIndexSysTypeColData> findAllByIndexcode(@Param("index_code") String index_code, @Param("time") String time, @Param("diquName") String diquName);

    //根据指标code查询所有季度数据
    @Query("select t.index_name, t.time, t.jh_wcl, t.jh_zs, t.sj_wcl, t.sj_zs,t.diqu_name from TIndexSysTypeColData t where t.index_code = :index_code and t.diqu_name =:diquName order by t.time asc")
    List<TIndexSysTypeColData> findAllByIndexcodeAndDiqu_name(@Param("index_code") String index_code, @Param("diquName") String diquName);

    //查询所有责任部门
    @Query("select distinct t.diqu_name from TIndexSysTypeColData t where t.diqu is not null")
    List<TIndexSysTypeColData> findAllDiqu_Name();

    //分指标类型查询指标数据
    @Query("select t.index_name,t.index_code,t.time,sum(t.jh_wcl) as jh_wcl,sum(t.jh_zs) as jh_zs,sum(t.sj_wcl) as sj_wcl,sum(t.sj_zs) as sj_zs from TIndexSysTypeColData t group by t.index_name,t.index_code,t.time order by t.time asc")
    List<TIndexSysTypeColData> findByGroup();

    //按部门查询指标
    @Query("select t.index_name,t.index_code,t.time,sum(t.jh_wcl) as jh_wcl,sum(t.jh_zs) as jh_zs,sum(t.sj_wcl) as sj_wcl,sum(t.sj_zs) as sj_zs from TIndexSysTypeColData t where t.diqu_name = :dep  group by t.index_name,t.index_code,t.time order by t.time asc")
    List<TIndexSysTypeColData> findByDiqu_nameGroupByDep(@Param("dep") String dep);
}
